<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<!--[if IE]><meta http-equiv="X-UA-Compatible" content="IE=edge"><![endif]-->
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta name="generator" content="Asciidoctor 1.5.5">
<title>MySQL</title>
<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Open+Sans:300,300italic,400,400italic,600,600italic%7CNoto+Serif:400,400italic,700,700italic%7CDroid+Sans+Mono:400,700">
<style>
/* Asciidoctor default stylesheet | MIT License | http://asciidoctor.org */
/* Remove comment around @import statement below when using as a custom stylesheet */
/*@import "https://fonts.googleapis.com/css?family=Open+Sans:300,300italic,400,400italic,600,600italic%7CNoto+Serif:400,400italic,700,700italic%7CDroid+Sans+Mono:400,700";*/
article,aside,details,figcaption,figure,footer,header,hgroup,main,nav,section,summary{display:block}
audio,canvas,video{display:inline-block}
audio:not([controls]){display:none;height:0}
[hidden],template{display:none}
script{display:none!important}
html{font-family:sans-serif;-ms-text-size-adjust:100%;-webkit-text-size-adjust:100%}
a{background:transparent}
a:focus{outline:thin dotted}
a:active,a:hover{outline:0}
h1{font-size:2em;margin:.67em 0}
abbr[title]{border-bottom:1px dotted}
b,strong{font-weight:bold}
dfn{font-style:italic}
hr{-moz-box-sizing:content-box;box-sizing:content-box;height:0}
mark{background:#ff0;color:#000}
code,kbd,pre,samp{font-family:monospace;font-size:1em}
pre{white-space:pre-wrap}
q{quotes:"\201C" "\201D" "\2018" "\2019"}
small{font-size:80%}
sub,sup{font-size:75%;line-height:0;position:relative;vertical-align:baseline}
sup{top:-.5em}
sub{bottom:-.25em}
img{border:0}
svg:not(:root){overflow:hidden}
figure{margin:0}
fieldset{border:1px solid silver;margin:0 2px;padding:.35em .625em .75em}
legend{border:0;padding:0}
button,input,select,textarea{font-family:inherit;font-size:100%;margin:0}
button,input{line-height:normal}
button,select{text-transform:none}
button,html input[type="button"],input[type="reset"],input[type="submit"]{-webkit-appearance:button;cursor:pointer}
button[disabled],html input[disabled]{cursor:default}
input[type="checkbox"],input[type="radio"]{box-sizing:border-box;padding:0}
input[type="search"]{-webkit-appearance:textfield;-moz-box-sizing:content-box;-webkit-box-sizing:content-box;box-sizing:content-box}
input[type="search"]::-webkit-search-cancel-button,input[type="search"]::-webkit-search-decoration{-webkit-appearance:none}
button::-moz-focus-inner,input::-moz-focus-inner{border:0;padding:0}
textarea{overflow:auto;vertical-align:top}
table{border-collapse:collapse;border-spacing:0}
*,*:before,*:after{-moz-box-sizing:border-box;-webkit-box-sizing:border-box;box-sizing:border-box}
html,body{font-size:100%}
body{background:#fff;color:rgba(0,0,0,.8);padding:0;margin:0;font-family:"Noto Serif","DejaVu Serif",serif;font-weight:400;font-style:normal;line-height:1;position:relative;cursor:auto;tab-size:4;-moz-osx-font-smoothing:grayscale;-webkit-font-smoothing:antialiased}
a:hover{cursor:pointer}
img,object,embed{max-width:100%;height:auto}
object,embed{height:100%}
img{-ms-interpolation-mode:bicubic}
.left{float:left!important}
.right{float:right!important}
.text-left{text-align:left!important}
.text-right{text-align:right!important}
.text-center{text-align:center!important}
.text-justify{text-align:justify!important}
.hide{display:none}
img,object,svg{display:inline-block;vertical-align:middle}
textarea{height:auto;min-height:50px}
select{width:100%}
.center{margin-left:auto;margin-right:auto}
.spread{width:100%}
p.lead,.paragraph.lead>p,#preamble>.sectionbody>.paragraph:first-of-type p{font-size:1.21875em;line-height:1.6}
.subheader,.admonitionblock td.content>.title,.audioblock>.title,.exampleblock>.title,.imageblock>.title,.listingblock>.title,.literalblock>.title,.stemblock>.title,.openblock>.title,.paragraph>.title,.quoteblock>.title,table.tableblock>.title,.verseblock>.title,.videoblock>.title,.dlist>.title,.olist>.title,.ulist>.title,.qlist>.title,.hdlist>.title{line-height:1.45;color:#7a2518;font-weight:400;margin-top:0;margin-bottom:.25em}
div,dl,dt,dd,ul,ol,li,h1,h2,h3,#toctitle,.sidebarblock>.content>.title,h4,h5,h6,pre,form,p,blockquote,th,td{margin:0;padding:0;direction:ltr}
a{color:#2156a5;text-decoration:underline;line-height:inherit}
a:hover,a:focus{color:#1d4b8f}
a img{border:none}
p{font-family:inherit;font-weight:400;font-size:1em;line-height:1.6;margin-bottom:1.25em;text-rendering:optimizeLegibility}
p aside{font-size:.875em;line-height:1.35;font-style:italic}
h1,h2,h3,#toctitle,.sidebarblock>.content>.title,h4,h5,h6{font-family:"Open Sans","DejaVu Sans",sans-serif;font-weight:300;font-style:normal;color:#ba3925;text-rendering:optimizeLegibility;margin-top:1em;margin-bottom:.5em;line-height:1.0125em}
h1 small,h2 small,h3 small,#toctitle small,.sidebarblock>.content>.title small,h4 small,h5 small,h6 small{font-size:60%;color:#e99b8f;line-height:0}
h1{font-size:2.125em}
h2{font-size:1.6875em}
h3,#toctitle,.sidebarblock>.content>.title{font-size:1.375em}
h4,h5{font-size:1.125em}
h6{font-size:1em}
hr{border:solid #ddddd8;border-width:1px 0 0;clear:both;margin:1.25em 0 1.1875em;height:0}
em,i{font-style:italic;line-height:inherit}
strong,b{font-weight:bold;line-height:inherit}
small{font-size:60%;line-height:inherit}
code{font-family:"Droid Sans Mono","DejaVu Sans Mono",monospace;font-weight:400;color:rgba(0,0,0,.9)}
ul,ol,dl{font-size:1em;line-height:1.6;margin-bottom:1.25em;list-style-position:outside;font-family:inherit}
ul,ol,ul.no-bullet,ol.no-bullet{margin-left:1.5em}
ul li ul,ul li ol{margin-left:1.25em;margin-bottom:0;font-size:1em}
ul.square li ul,ul.circle li ul,ul.disc li ul{list-style:inherit}
ul.square{list-style-type:square}
ul.circle{list-style-type:circle}
ul.disc{list-style-type:disc}
ul.no-bullet{list-style:none}
ol li ul,ol li ol{margin-left:1.25em;margin-bottom:0}
dl dt{margin-bottom:.3125em;font-weight:bold}
dl dd{margin-bottom:1.25em}
abbr,acronym{text-transform:uppercase;font-size:90%;color:rgba(0,0,0,.8);border-bottom:1px dotted #ddd;cursor:help}
abbr{text-transform:none}
blockquote{margin:0 0 1.25em;padding:.5625em 1.25em 0 1.1875em;border-left:1px solid #ddd}
blockquote cite{display:block;font-size:.9375em;color:rgba(0,0,0,.6)}
blockquote cite:before{content:"\2014 \0020"}
blockquote cite a,blockquote cite a:visited{color:rgba(0,0,0,.6)}
blockquote,blockquote p{line-height:1.6;color:rgba(0,0,0,.85)}
@media only screen and (min-width:768px){h1,h2,h3,#toctitle,.sidebarblock>.content>.title,h4,h5,h6{line-height:1.2}
h1{font-size:2.75em}
h2{font-size:2.3125em}
h3,#toctitle,.sidebarblock>.content>.title{font-size:1.6875em}
h4{font-size:1.4375em}}
table{background:#fff;margin-bottom:1.25em;border:solid 1px #dedede}
table thead,table tfoot{background:#f7f8f7;font-weight:bold}
table thead tr th,table thead tr td,table tfoot tr th,table tfoot tr td{padding:.5em .625em .625em;font-size:inherit;color:rgba(0,0,0,.8);text-align:left}
table tr th,table tr td{padding:.5625em .625em;font-size:inherit;color:rgba(0,0,0,.8)}
table tr.even,table tr.alt,table tr:nth-of-type(even){background:#f8f8f7}
table thead tr th,table tfoot tr th,table tbody tr td,table tr td,table tfoot tr td{display:table-cell;line-height:1.6}
h1,h2,h3,#toctitle,.sidebarblock>.content>.title,h4,h5,h6{line-height:1.2;word-spacing:-.05em}
h1 strong,h2 strong,h3 strong,#toctitle strong,.sidebarblock>.content>.title strong,h4 strong,h5 strong,h6 strong{font-weight:400}
.clearfix:before,.clearfix:after,.float-group:before,.float-group:after{content:" ";display:table}
.clearfix:after,.float-group:after{clear:both}
*:not(pre)>code{font-size:.9375em;font-style:normal!important;letter-spacing:0;padding:.1em .5ex;word-spacing:-.15em;background-color:#f7f7f8;-webkit-border-radius:4px;border-radius:4px;line-height:1.45;text-rendering:optimizeSpeed;word-wrap:break-word}
*:not(pre)>code.nobreak{word-wrap:normal}
*:not(pre)>code.nowrap{white-space:nowrap}
pre,pre>code{line-height:1.45;color:rgba(0,0,0,.9);font-family:"Droid Sans Mono","DejaVu Sans Mono",monospace;font-weight:400;text-rendering:optimizeSpeed}
em em{font-style:normal}
strong strong{font-weight:400}
.keyseq{color:rgba(51,51,51,.8)}
kbd{font-family:"Droid Sans Mono","DejaVu Sans Mono",monospace;display:inline-block;color:rgba(0,0,0,.8);font-size:.65em;line-height:1.45;background-color:#f7f7f7;border:1px solid #ccc;-webkit-border-radius:3px;border-radius:3px;-webkit-box-shadow:0 1px 0 rgba(0,0,0,.2),0 0 0 .1em white inset;box-shadow:0 1px 0 rgba(0,0,0,.2),0 0 0 .1em #fff inset;margin:0 .15em;padding:.2em .5em;vertical-align:middle;position:relative;top:-.1em;white-space:nowrap}
.keyseq kbd:first-child{margin-left:0}
.keyseq kbd:last-child{margin-right:0}
.menuseq,.menu{color:rgba(0,0,0,.8)}
b.button:before,b.button:after{position:relative;top:-1px;font-weight:400}
b.button:before{content:"[";padding:0 3px 0 2px}
b.button:after{content:"]";padding:0 2px 0 3px}
p a>code:hover{color:rgba(0,0,0,.9)}
#header,#content,#footnotes,#footer{width:100%;margin-left:auto;margin-right:auto;margin-top:0;margin-bottom:0;max-width:62.5em;*zoom:1;position:relative;padding-left:.9375em;padding-right:.9375em}
#header:before,#header:after,#content:before,#content:after,#footnotes:before,#footnotes:after,#footer:before,#footer:after{content:" ";display:table}
#header:after,#content:after,#footnotes:after,#footer:after{clear:both}
#content{margin-top:1.25em}
#content:before{content:none}
#header>h1:first-child{color:rgba(0,0,0,.85);margin-top:2.25rem;margin-bottom:0}
#header>h1:first-child+#toc{margin-top:8px;border-top:1px solid #ddddd8}
#header>h1:only-child,body.toc2 #header>h1:nth-last-child(2){border-bottom:1px solid #ddddd8;padding-bottom:8px}
#header .details{border-bottom:1px solid #ddddd8;line-height:1.45;padding-top:.25em;padding-bottom:.25em;padding-left:.25em;color:rgba(0,0,0,.6);display:-ms-flexbox;display:-webkit-flex;display:flex;-ms-flex-flow:row wrap;-webkit-flex-flow:row wrap;flex-flow:row wrap}
#header .details span:first-child{margin-left:-.125em}
#header .details span.email a{color:rgba(0,0,0,.85)}
#header .details br{display:none}
#header .details br+span:before{content:"\00a0\2013\00a0"}
#header .details br+span.author:before{content:"\00a0\22c5\00a0";color:rgba(0,0,0,.85)}
#header .details br+span#revremark:before{content:"\00a0|\00a0"}
#header #revnumber{text-transform:capitalize}
#header #revnumber:after{content:"\00a0"}
#content>h1:first-child:not([class]){color:rgba(0,0,0,.85);border-bottom:1px solid #ddddd8;padding-bottom:8px;margin-top:0;padding-top:1rem;margin-bottom:1.25rem}
#toc{border-bottom:1px solid #efefed;padding-bottom:.5em}
#toc>ul{margin-left:.125em}
#toc ul.sectlevel0>li>a{font-style:italic}
#toc ul.sectlevel0 ul.sectlevel1{margin:.5em 0}
#toc ul{font-family:"Open Sans","DejaVu Sans",sans-serif;list-style-type:none}
#toc li{line-height:1.3334;margin-top:.3334em}
#toc a{text-decoration:none}
#toc a:active{text-decoration:underline}
#toctitle{color:#7a2518;font-size:1.2em}
@media only screen and (min-width:768px){#toctitle{font-size:1.375em}
body.toc2{padding-left:15em;padding-right:0}
#toc.toc2{margin-top:0!important;background-color:#f8f8f7;position:fixed;width:15em;left:0;top:0;border-right:1px solid #efefed;border-top-width:0!important;border-bottom-width:0!important;z-index:1000;padding:1.25em 1em;height:100%;overflow:auto}
#toc.toc2 #toctitle{margin-top:0;margin-bottom:.8rem;font-size:1.2em}
#toc.toc2>ul{font-size:.9em;margin-bottom:0}
#toc.toc2 ul ul{margin-left:0;padding-left:1em}
#toc.toc2 ul.sectlevel0 ul.sectlevel1{padding-left:0;margin-top:.5em;margin-bottom:.5em}
body.toc2.toc-right{padding-left:0;padding-right:15em}
body.toc2.toc-right #toc.toc2{border-right-width:0;border-left:1px solid #efefed;left:auto;right:0}}
@media only screen and (min-width:1280px){body.toc2{padding-left:20em;padding-right:0}
#toc.toc2{width:20em}
#toc.toc2 #toctitle{font-size:1.375em}
#toc.toc2>ul{font-size:.95em}
#toc.toc2 ul ul{padding-left:1.25em}
body.toc2.toc-right{padding-left:0;padding-right:20em}}
#content #toc{border-style:solid;border-width:1px;border-color:#e0e0dc;margin-bottom:1.25em;padding:1.25em;background:#f8f8f7;-webkit-border-radius:4px;border-radius:4px}
#content #toc>:first-child{margin-top:0}
#content #toc>:last-child{margin-bottom:0}
#footer{max-width:100%;background-color:rgba(0,0,0,.8);padding:1.25em}
#footer-text{color:rgba(255,255,255,.8);line-height:1.44}
.sect1{padding-bottom:.625em}
@media only screen and (min-width:768px){.sect1{padding-bottom:1.25em}}
.sect1+.sect1{border-top:1px solid #efefed}
#content h1>a.anchor,h2>a.anchor,h3>a.anchor,#toctitle>a.anchor,.sidebarblock>.content>.title>a.anchor,h4>a.anchor,h5>a.anchor,h6>a.anchor{position:absolute;z-index:1001;width:1.5ex;margin-left:-1.5ex;display:block;text-decoration:none!important;visibility:hidden;text-align:center;font-weight:400}
#content h1>a.anchor:before,h2>a.anchor:before,h3>a.anchor:before,#toctitle>a.anchor:before,.sidebarblock>.content>.title>a.anchor:before,h4>a.anchor:before,h5>a.anchor:before,h6>a.anchor:before{content:"\00A7";font-size:.85em;display:block;padding-top:.1em}
#content h1:hover>a.anchor,#content h1>a.anchor:hover,h2:hover>a.anchor,h2>a.anchor:hover,h3:hover>a.anchor,#toctitle:hover>a.anchor,.sidebarblock>.content>.title:hover>a.anchor,h3>a.anchor:hover,#toctitle>a.anchor:hover,.sidebarblock>.content>.title>a.anchor:hover,h4:hover>a.anchor,h4>a.anchor:hover,h5:hover>a.anchor,h5>a.anchor:hover,h6:hover>a.anchor,h6>a.anchor:hover{visibility:visible}
#content h1>a.link,h2>a.link,h3>a.link,#toctitle>a.link,.sidebarblock>.content>.title>a.link,h4>a.link,h5>a.link,h6>a.link{color:#ba3925;text-decoration:none}
#content h1>a.link:hover,h2>a.link:hover,h3>a.link:hover,#toctitle>a.link:hover,.sidebarblock>.content>.title>a.link:hover,h4>a.link:hover,h5>a.link:hover,h6>a.link:hover{color:#a53221}
.audioblock,.imageblock,.literalblock,.listingblock,.stemblock,.videoblock{margin-bottom:1.25em}
.admonitionblock td.content>.title,.audioblock>.title,.exampleblock>.title,.imageblock>.title,.listingblock>.title,.literalblock>.title,.stemblock>.title,.openblock>.title,.paragraph>.title,.quoteblock>.title,table.tableblock>.title,.verseblock>.title,.videoblock>.title,.dlist>.title,.olist>.title,.ulist>.title,.qlist>.title,.hdlist>.title{text-rendering:optimizeLegibility;text-align:left;font-family:"Noto Serif","DejaVu Serif",serif;font-size:1rem;font-style:italic}
table.tableblock>caption.title{white-space:nowrap;overflow:visible;max-width:0}
.paragraph.lead>p,#preamble>.sectionbody>.paragraph:first-of-type p{color:rgba(0,0,0,.85)}
table.tableblock #preamble>.sectionbody>.paragraph:first-of-type p{font-size:inherit}
.admonitionblock>table{border-collapse:separate;border:0;background:none;width:100%}
.admonitionblock>table td.icon{text-align:center;width:80px}
.admonitionblock>table td.icon img{max-width:none}
.admonitionblock>table td.icon .title{font-weight:bold;font-family:"Open Sans","DejaVu Sans",sans-serif;text-transform:uppercase}
.admonitionblock>table td.content{padding-left:1.125em;padding-right:1.25em;border-left:1px solid #ddddd8;color:rgba(0,0,0,.6)}
.admonitionblock>table td.content>:last-child>:last-child{margin-bottom:0}
.exampleblock>.content{border-style:solid;border-width:1px;border-color:#e6e6e6;margin-bottom:1.25em;padding:1.25em;background:#fff;-webkit-border-radius:4px;border-radius:4px}
.exampleblock>.content>:first-child{margin-top:0}
.exampleblock>.content>:last-child{margin-bottom:0}
.sidebarblock{border-style:solid;border-width:1px;border-color:#e0e0dc;margin-bottom:1.25em;padding:1.25em;background:#f8f8f7;-webkit-border-radius:4px;border-radius:4px}
.sidebarblock>:first-child{margin-top:0}
.sidebarblock>:last-child{margin-bottom:0}
.sidebarblock>.content>.title{color:#7a2518;margin-top:0;text-align:center}
.exampleblock>.content>:last-child>:last-child,.exampleblock>.content .olist>ol>li:last-child>:last-child,.exampleblock>.content .ulist>ul>li:last-child>:last-child,.exampleblock>.content .qlist>ol>li:last-child>:last-child,.sidebarblock>.content>:last-child>:last-child,.sidebarblock>.content .olist>ol>li:last-child>:last-child,.sidebarblock>.content .ulist>ul>li:last-child>:last-child,.sidebarblock>.content .qlist>ol>li:last-child>:last-child{margin-bottom:0}
.literalblock pre,.listingblock pre:not(.highlight),.listingblock pre[class="highlight"],.listingblock pre[class^="highlight "],.listingblock pre.CodeRay,.listingblock pre.prettyprint{background:#f7f7f8}
.sidebarblock .literalblock pre,.sidebarblock .listingblock pre:not(.highlight),.sidebarblock .listingblock pre[class="highlight"],.sidebarblock .listingblock pre[class^="highlight "],.sidebarblock .listingblock pre.CodeRay,.sidebarblock .listingblock pre.prettyprint{background:#f2f1f1}
.literalblock pre,.literalblock pre[class],.listingblock pre,.listingblock pre[class]{-webkit-border-radius:4px;border-radius:4px;word-wrap:break-word;padding:1em;font-size:.8125em}
.literalblock pre.nowrap,.literalblock pre[class].nowrap,.listingblock pre.nowrap,.listingblock pre[class].nowrap{overflow-x:auto;white-space:pre;word-wrap:normal}
@media only screen and (min-width:768px){.literalblock pre,.literalblock pre[class],.listingblock pre,.listingblock pre[class]{font-size:.90625em}}
@media only screen and (min-width:1280px){.literalblock pre,.literalblock pre[class],.listingblock pre,.listingblock pre[class]{font-size:1em}}
.literalblock.output pre{color:#f7f7f8;background-color:rgba(0,0,0,.9)}
.listingblock pre.highlightjs{padding:0}
.listingblock pre.highlightjs>code{padding:1em;-webkit-border-radius:4px;border-radius:4px}
.listingblock pre.prettyprint{border-width:0}
.listingblock>.content{position:relative}
.listingblock code[data-lang]:before{display:none;content:attr(data-lang);position:absolute;font-size:.75em;top:.425rem;right:.5rem;line-height:1;text-transform:uppercase;color:#999}
.listingblock:hover code[data-lang]:before{display:block}
.listingblock.terminal pre .command:before{content:attr(data-prompt);padding-right:.5em;color:#999}
.listingblock.terminal pre .command:not([data-prompt]):before{content:"$"}
table.pyhltable{border-collapse:separate;border:0;margin-bottom:0;background:none}
table.pyhltable td{vertical-align:top;padding-top:0;padding-bottom:0;line-height:1.45}
table.pyhltable td.code{padding-left:.75em;padding-right:0}
pre.pygments .lineno,table.pyhltable td:not(.code){color:#999;padding-left:0;padding-right:.5em;border-right:1px solid #ddddd8}
pre.pygments .lineno{display:inline-block;margin-right:.25em}
table.pyhltable .linenodiv{background:none!important;padding-right:0!important}
.quoteblock{margin:0 1em 1.25em 1.5em;display:table}
.quoteblock>.title{margin-left:-1.5em;margin-bottom:.75em}
.quoteblock blockquote,.quoteblock blockquote p{color:rgba(0,0,0,.85);font-size:1.15rem;line-height:1.75;word-spacing:.1em;letter-spacing:0;font-style:italic;text-align:justify}
.quoteblock blockquote{margin:0;padding:0;border:0}
.quoteblock blockquote:before{content:"\201c";float:left;font-size:2.75em;font-weight:bold;line-height:.6em;margin-left:-.6em;color:#7a2518;text-shadow:0 1px 2px rgba(0,0,0,.1)}
.quoteblock blockquote>.paragraph:last-child p{margin-bottom:0}
.quoteblock .attribution{margin-top:.5em;margin-right:.5ex;text-align:right}
.quoteblock .quoteblock{margin-left:0;margin-right:0;padding:.5em 0;border-left:3px solid rgba(0,0,0,.6)}
.quoteblock .quoteblock blockquote{padding:0 0 0 .75em}
.quoteblock .quoteblock blockquote:before{display:none}
.verseblock{margin:0 1em 1.25em 1em}
.verseblock pre{font-family:"Open Sans","DejaVu Sans",sans;font-size:1.15rem;color:rgba(0,0,0,.85);font-weight:300;text-rendering:optimizeLegibility}
.verseblock pre strong{font-weight:400}
.verseblock .attribution{margin-top:1.25rem;margin-left:.5ex}
.quoteblock .attribution,.verseblock .attribution{font-size:.9375em;line-height:1.45;font-style:italic}
.quoteblock .attribution br,.verseblock .attribution br{display:none}
.quoteblock .attribution cite,.verseblock .attribution cite{display:block;letter-spacing:-.025em;color:rgba(0,0,0,.6)}
.quoteblock.abstract{margin:0 0 1.25em 0;display:block}
.quoteblock.abstract blockquote,.quoteblock.abstract blockquote p{text-align:left;word-spacing:0}
.quoteblock.abstract blockquote:before,.quoteblock.abstract blockquote p:first-of-type:before{display:none}
table.tableblock{max-width:100%;border-collapse:separate}
table.tableblock td>.paragraph:last-child p>p:last-child,table.tableblock th>p:last-child,table.tableblock td>p:last-child{margin-bottom:0}
table.tableblock,th.tableblock,td.tableblock{border:0 solid #dedede}
table.grid-all th.tableblock,table.grid-all td.tableblock{border-width:0 1px 1px 0}
table.grid-all tfoot>tr>th.tableblock,table.grid-all tfoot>tr>td.tableblock{border-width:1px 1px 0 0}
table.grid-cols th.tableblock,table.grid-cols td.tableblock{border-width:0 1px 0 0}
table.grid-all *>tr>.tableblock:last-child,table.grid-cols *>tr>.tableblock:last-child{border-right-width:0}
table.grid-rows th.tableblock,table.grid-rows td.tableblock{border-width:0 0 1px 0}
table.grid-all tbody>tr:last-child>th.tableblock,table.grid-all tbody>tr:last-child>td.tableblock,table.grid-all thead:last-child>tr>th.tableblock,table.grid-rows tbody>tr:last-child>th.tableblock,table.grid-rows tbody>tr:last-child>td.tableblock,table.grid-rows thead:last-child>tr>th.tableblock{border-bottom-width:0}
table.grid-rows tfoot>tr>th.tableblock,table.grid-rows tfoot>tr>td.tableblock{border-width:1px 0 0 0}
table.frame-all{border-width:1px}
table.frame-sides{border-width:0 1px}
table.frame-topbot{border-width:1px 0}
th.halign-left,td.halign-left{text-align:left}
th.halign-right,td.halign-right{text-align:right}
th.halign-center,td.halign-center{text-align:center}
th.valign-top,td.valign-top{vertical-align:top}
th.valign-bottom,td.valign-bottom{vertical-align:bottom}
th.valign-middle,td.valign-middle{vertical-align:middle}
table thead th,table tfoot th{font-weight:bold}
tbody tr th{display:table-cell;line-height:1.6;background:#f7f8f7}
tbody tr th,tbody tr th p,tfoot tr th,tfoot tr th p{color:rgba(0,0,0,.8);font-weight:bold}
p.tableblock>code:only-child{background:none;padding:0}
p.tableblock{font-size:1em}
td>div.verse{white-space:pre}
ol{margin-left:1.75em}
ul li ol{margin-left:1.5em}
dl dd{margin-left:1.125em}
dl dd:last-child,dl dd:last-child>:last-child{margin-bottom:0}
ol>li p,ul>li p,ul dd,ol dd,.olist .olist,.ulist .ulist,.ulist .olist,.olist .ulist{margin-bottom:.625em}
ul.unstyled,ol.unnumbered,ul.checklist,ul.none{list-style-type:none}
ul.unstyled,ol.unnumbered,ul.checklist{margin-left:.625em}
ul.checklist li>p:first-child>.fa-square-o:first-child,ul.checklist li>p:first-child>.fa-check-square-o:first-child{width:1em;font-size:.85em}
ul.checklist li>p:first-child>input[type="checkbox"]:first-child{width:1em;position:relative;top:1px}
ul.inline{margin:0 auto .625em auto;margin-left:-1.375em;margin-right:0;padding:0;list-style:none;overflow:hidden}
ul.inline>li{list-style:none;float:left;margin-left:1.375em;display:block}
ul.inline>li>*{display:block}
.unstyled dl dt{font-weight:400;font-style:normal}
ol.arabic{list-style-type:decimal}
ol.decimal{list-style-type:decimal-leading-zero}
ol.loweralpha{list-style-type:lower-alpha}
ol.upperalpha{list-style-type:upper-alpha}
ol.lowerroman{list-style-type:lower-roman}
ol.upperroman{list-style-type:upper-roman}
ol.lowergreek{list-style-type:lower-greek}
.hdlist>table,.colist>table{border:0;background:none}
.hdlist>table>tbody>tr,.colist>table>tbody>tr{background:none}
td.hdlist1,td.hdlist2{vertical-align:top;padding:0 .625em}
td.hdlist1{font-weight:bold;padding-bottom:1.25em}
.literalblock+.colist,.listingblock+.colist{margin-top:-.5em}
.colist>table tr>td:first-of-type{padding:0 .75em;line-height:1}
.colist>table tr>td:last-of-type{padding:.25em 0}
.thumb,.th{line-height:0;display:inline-block;border:solid 4px #fff;-webkit-box-shadow:0 0 0 1px #ddd;box-shadow:0 0 0 1px #ddd}
.imageblock.left,.imageblock[style*="float: left"]{margin:.25em .625em 1.25em 0}
.imageblock.right,.imageblock[style*="float: right"]{margin:.25em 0 1.25em .625em}
.imageblock>.title{margin-bottom:0}
.imageblock.thumb,.imageblock.th{border-width:6px}
.imageblock.thumb>.title,.imageblock.th>.title{padding:0 .125em}
.image.left,.image.right{margin-top:.25em;margin-bottom:.25em;display:inline-block;line-height:0}
.image.left{margin-right:.625em}
.image.right{margin-left:.625em}
a.image{text-decoration:none;display:inline-block}
a.image object{pointer-events:none}
sup.footnote,sup.footnoteref{font-size:.875em;position:static;vertical-align:super}
sup.footnote a,sup.footnoteref a{text-decoration:none}
sup.footnote a:active,sup.footnoteref a:active{text-decoration:underline}
#footnotes{padding-top:.75em;padding-bottom:.75em;margin-bottom:.625em}
#footnotes hr{width:20%;min-width:6.25em;margin:-.25em 0 .75em 0;border-width:1px 0 0 0}
#footnotes .footnote{padding:0 .375em 0 .225em;line-height:1.3334;font-size:.875em;margin-left:1.2em;text-indent:-1.05em;margin-bottom:.2em}
#footnotes .footnote a:first-of-type{font-weight:bold;text-decoration:none}
#footnotes .footnote:last-of-type{margin-bottom:0}
#content #footnotes{margin-top:-.625em;margin-bottom:0;padding:.75em 0}
.gist .file-data>table{border:0;background:#fff;width:100%;margin-bottom:0}
.gist .file-data>table td.line-data{width:99%}
div.unbreakable{page-break-inside:avoid}
.big{font-size:larger}
.small{font-size:smaller}
.underline{text-decoration:underline}
.overline{text-decoration:overline}
.line-through{text-decoration:line-through}
.aqua{color:#00bfbf}
.aqua-background{background-color:#00fafa}
.black{color:#000}
.black-background{background-color:#000}
.blue{color:#0000bf}
.blue-background{background-color:#0000fa}
.fuchsia{color:#bf00bf}
.fuchsia-background{background-color:#fa00fa}
.gray{color:#606060}
.gray-background{background-color:#7d7d7d}
.green{color:#006000}
.green-background{background-color:#007d00}
.lime{color:#00bf00}
.lime-background{background-color:#00fa00}
.maroon{color:#600000}
.maroon-background{background-color:#7d0000}
.navy{color:#000060}
.navy-background{background-color:#00007d}
.olive{color:#606000}
.olive-background{background-color:#7d7d00}
.purple{color:#600060}
.purple-background{background-color:#7d007d}
.red{color:#bf0000}
.red-background{background-color:#fa0000}
.silver{color:#909090}
.silver-background{background-color:#bcbcbc}
.teal{color:#006060}
.teal-background{background-color:#007d7d}
.white{color:#bfbfbf}
.white-background{background-color:#fafafa}
.yellow{color:#bfbf00}
.yellow-background{background-color:#fafa00}
span.icon>.fa{cursor:default}
.admonitionblock td.icon [class^="fa icon-"]{font-size:2.5em;text-shadow:1px 1px 2px rgba(0,0,0,.5);cursor:default}
.admonitionblock td.icon .icon-note:before{content:"\f05a";color:#19407c}
.admonitionblock td.icon .icon-tip:before{content:"\f0eb";text-shadow:1px 1px 2px rgba(155,155,0,.8);color:#111}
.admonitionblock td.icon .icon-warning:before{content:"\f071";color:#bf6900}
.admonitionblock td.icon .icon-caution:before{content:"\f06d";color:#bf3400}
.admonitionblock td.icon .icon-important:before{content:"\f06a";color:#bf0000}
.conum[data-value]{display:inline-block;color:#fff!important;background-color:rgba(0,0,0,.8);-webkit-border-radius:100px;border-radius:100px;text-align:center;font-size:.75em;width:1.67em;height:1.67em;line-height:1.67em;font-family:"Open Sans","DejaVu Sans",sans-serif;font-style:normal;font-weight:bold}
.conum[data-value] *{color:#fff!important}
.conum[data-value]+b{display:none}
.conum[data-value]:after{content:attr(data-value)}
pre .conum[data-value]{position:relative;top:-.125em}
b.conum *{color:inherit!important}
.conum:not([data-value]):empty{display:none}
dt,th.tableblock,td.content,div.footnote{text-rendering:optimizeLegibility}
h1,h2,p,td.content,span.alt{letter-spacing:-.01em}
p strong,td.content strong,div.footnote strong{letter-spacing:-.005em}
p,blockquote,dt,td.content,span.alt{font-size:1.0625rem}
p{margin-bottom:1.25rem}
.sidebarblock p,.sidebarblock dt,.sidebarblock td.content,p.tableblock{font-size:1em}
.exampleblock>.content{background-color:#fffef7;border-color:#e0e0dc;-webkit-box-shadow:0 1px 4px #e0e0dc;box-shadow:0 1px 4px #e0e0dc}
.print-only{display:none!important}
@media print{@page{margin:1.25cm .75cm}
*{-webkit-box-shadow:none!important;box-shadow:none!important;text-shadow:none!important}
a{color:inherit!important;text-decoration:underline!important}
a.bare,a[href^="#"],a[href^="mailto:"]{text-decoration:none!important}
a[href^="http:"]:not(.bare):after,a[href^="https:"]:not(.bare):after{content:"(" attr(href) ")";display:inline-block;font-size:.875em;padding-left:.25em}
abbr[title]:after{content:" (" attr(title) ")"}
pre,blockquote,tr,img,object,svg{page-break-inside:avoid}
thead{display:table-header-group}
svg{max-width:100%}
p,blockquote,dt,td.content{font-size:1em;orphans:3;widows:3}
h2,h3,#toctitle,.sidebarblock>.content>.title{page-break-after:avoid}
#toc,.sidebarblock,.exampleblock>.content{background:none!important}
#toc{border-bottom:1px solid #ddddd8!important;padding-bottom:0!important}
.sect1{padding-bottom:0!important}
.sect1+.sect1{border:0!important}
#header>h1:first-child{margin-top:1.25rem}
body.book #header{text-align:center}
body.book #header>h1:first-child{border:0!important;margin:2.5em 0 1em 0}
body.book #header .details{border:0!important;display:block;padding:0!important}
body.book #header .details span:first-child{margin-left:0!important}
body.book #header .details br{display:block}
body.book #header .details br+span:before{content:none!important}
body.book #toc{border:0!important;text-align:left!important;padding:0!important;margin:0!important}
body.book #toc,body.book #preamble,body.book h1.sect0,body.book .sect1>h2{page-break-before:always}
.listingblock code[data-lang]:before{display:block}
#footer{background:none!important;padding:0 .9375em}
#footer-text{color:rgba(0,0,0,.6)!important;font-size:.9em}
.hide-on-print{display:none!important}
.print-only{display:block!important}
.hide-for-print{display:none!important}
.show-for-print{display:inherit!important}}
</style>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.6.3/css/font-awesome.min.css">
</head>
<body class="book">
<div id="header">
<h1>MySQL</h1>
<div id="toc" class="toc">
<div id="toctitle">MySQL</div>
<ul class="sectlevel1">
<li><a href="#_case">1. case</a>
<ul class="sectlevel2">
<li><a href="#_mysql_存储过程">1.1. MySQL 存储过程</a></li>
<li><a href="#_mysql_常用函数">1.2. MySQL 常用函数</a></li>
<li><a href="#_mysql_存储过程基础">1.3. MySQL 存储过程基础</a></li>
<li><a href="#_mysql_深入研究记录">1.4. MySQL 深入研究记录</a></li>
</ul>
</li>
<li><a href="#_索引">2. 索引</a></li>
<li><a href="#_mysql_底层采用ｂ_tree_来存储索引">3. MySQL 底层采用Ｂ+ Tree 来存储索引</a></li>
<li><a href="#_innodb_和_myisam_存储引擎">4. InnoDB 和 MyISAM 存储引擎</a></li>
<li><a href="#_聚集索引和非聚集索引的区别">5. 聚集索引和非聚集索引的区别</a></li>
<li><a href="#_b_树_vs_b树">6. B+ 树 vs B树</a></li>
<li><a href="#_联合索引">7. 联合索引</a></li>
<li><a href="#_索引的设计原则">8. 索引的设计原则</a></li>
<li><a href="#_锁">9. 锁</a></li>
<li><a href="#_explain">10. explain</a>
<ul class="sectlevel2">
<li><a href="#_mysql_数据处理原理">10.1. MySQL 数据处理原理</a></li>
</ul>
</li>
</ul>
</div>
</div>
<div id="content">
<div class="sect1">
<h2 id="_case">1. case</h2>
<div class="sectionbody">
<div class="sect2">
<h3 id="_mysql_存储过程">1.1. MySQL 存储过程</h3>
<div class="paragraph">
<p><strong>需求</strong></p>
</div>
<div class="paragraph">
<p>有时候我们需要写一些逻辑代码来处理MySQL中的数据，尤其是在版本升级过程中，历史数据需要处理。这时候我们希望通过sql脚本就能完成数据的处理。</p>
</div>
<div class="paragraph">
<p>本例将介绍一个简单的业务逻辑</p>
</div>
<div class="paragraph">
<p><strong>准备工作</strong></p>
</div>
<div class="paragraph">
<p>创建3张表，一张学生表，一张班级表，一张学生与班级绑定关系表。 Version-1:
班级表中没有学生数量，导致每次查询班级学生数量都需要通过关联表统计。
Version-2: 新版本决定将学生数量统计出来，并存放到班级表中，方便查询。</p>
</div>
<div class="paragraph">
<p>这只是一个假想的需求，不要较真，只是演示如何通过sql实现。</p>
</div>
<div class="paragraph">
<p>Version-1 版本数据库</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-sql" data-lang="sql">CREATE TABLE flyway.student (
    id INT UNSIGNED auto_increment NOT NULL,
    name varchar(16) NULL,
    age TINYINT NULL,
    CONSTRAINT student_PK PRIMARY KEY (id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8
COLLATE=utf8_general_ci;

CREATE TABLE `class` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `class_name` varchar(16) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE flyway.student_with_class (
    id INT UNSIGNED auto_increment NOT NULL,
    student_id INT UNSIGNED NOT NULL,
    class_id INT UNSIGNED NOT NULL,
    CONSTRAINT student_with_class_PK PRIMARY KEY (id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8
COLLATE=utf8_general_ci;

INSERT INTO flyway.student
(name, age)
VALUES('小明', 7),('小王', 7),('小红', 7),('小花', 7);

INSERT INTO flyway.class
(class_name)
VALUES('一年级1班'),('一年级2班'),('一年级3班');

INSERT INTO flyway.student_with_class
(student_id, class_id)
VALUES(1, 1),(2, 2),(3, 3),(4, 1);</code></pre>
</div>
</div>
<div class="paragraph">
<p>Version-2 升级脚本</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-sql" data-lang="sql">-- 班级表新增学生数量字段
ALTER TABLE flyway.class ADD student_count INT UNSIGNED DEFAULT 0 NULL;</code></pre>
</div>
</div>
<div class="paragraph">
<p><strong>编写sql脚本处理数据</strong></p>
</div>
<div class="paragraph">
<p>思路：查询出所有班级，循环遍历每个班级，并对每个班级进行数量统计，然后更新这个班级的学生数量。</p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-sql" data-lang="sql">-- 删除存储过程（如果存在）
drop procedure if exists countStudent;

-- 定义语句结束符号，默认是逗号，每写一个语句以分号结尾，MySQL会立即执行，在这里不能这样做。
DELIMITER $$

create procedure countStudent()
begin
    --  这里声明一个 int 类型的本地变量，用于后面存放值
    declare studentCount int DEFAULT 0;
    declare classId int;
    declare className varchar(16);
    --  这里定义一个结束标识变量，当所有班级都更新后，就可以结束任务了。
    declare done TINYINT DEFAULT 0;

    --  将查询出来的班级列表定义，这里用 cursor 来表明
    declare classes cursor for select id, class_name from class;
    declare CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    --  开启 cursor
    open classes;
        --  这里开始循环处理每个班级
        class: LOOP
            --  这里是获取 classes 中的一行数据，并将其复制给定义好的变量上。注意保持和查询顺序一致
            fetch from classes into classId, className;
            --  如果处理完了，就停止循环
            IF done THEN LEAVE class; END IF;
            -- 这里开启事务，视情况而定
            start transaction;
                select count(*) into studentCount from student_with_class swc where swc.class_id = classId;
                update class set student_count = studentCount where id = classId and class_name = className;
            commit;
        end loop;
    close classes;

end $$

-- 恢复结束符号为分号
delimiter ;

-- 调用存储过程，也即执行我们的业务逻辑
call countStudent();

-- 如果是一次性的就删除。
drop procedure countStudent;</code></pre>
</div>
</div>
<div class="paragraph">
<p>注意事项：</p>
</div>
<div class="olist arabic">
<ol class="arabic">
<li>
<p>一定要使用 DELIMITER $$ 重新声明语句结束符号</p>
</li>
<li>
<p>declare 变量一定要在 cursor 以及 HANDLER 之前定义。</p>
</li>
</ol>
</div>
<div class="paragraph">
<p>难点（以前困扰我自己的）：</p>
</div>
<div class="olist arabic">
<ol class="arabic">
<li>
<p>查询出来的数据用什么东西接收： 通过 declare 声明变量</p>
</li>
<li>
<p>如何接收： 通过 into 关键字，将 select 中得字段存储在声明的变量中</p>
</li>
<li>
<p>列表数据如何处理： 循环 加上 获取每一行数据 （fetch … into）</p>
</li>
</ol>
</div>
<div class="paragraph">
<p>其他：</p>
</div>
<div class="olist arabic">
<ol class="arabic">
<li>
<p>业务逻辑根据实际情况在循环体中处理，需结合MySQL特有的语法</p>
</li>
</ol>
</div>
<div class="paragraph">
<p>总之，对我来说难点就是查询出来的数据如何处理，其他业务逻辑都有对应的语法。</p>
</div>
</div>
<div class="sect2">
<h3 id="_mysql_常用函数">1.2. MySQL 常用函数</h3>
<div class="paragraph">
<p>DATE_FORMAT(daily.create_time,&#8216;%Y-%m-%d&#8217;) = CURDATE()</p>
</div>
<div class="paragraph">
<p>current_time()</p>
</div>
</div>
<div class="sect2">
<h3 id="_mysql_存储过程基础">1.3. MySQL 存储过程基础</h3>
<div class="paragraph">
<p>摘要： - <a href="https://www.runoob.com/w3cnote/mysql-stored-procedure.html" class="bare">https://www.runoob.com/w3cnote/mysql-stored-procedure.html</a> -
<a href="https://blog.csdn.net/chinafire525/article/details/91374469" class="bare">https://blog.csdn.net/chinafire525/article/details/91374469</a> -
<a href="https://blog.csdn.net/weixin_39662432/article/details/112713981" class="bare">https://blog.csdn.net/weixin_39662432/article/details/112713981</a></p>
</div>
<div class="quoteblock">
<blockquote>
<div class="paragraph">
<p>存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。
简而言之，就是一次调用，可以实现多个sql语句，而且带有逻辑性。</p>
</div>
</blockquote>
</div>
<div class="paragraph">
<p><strong>复制</strong></p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-mysql" data-lang="mysql">DELIMITER  $$

#生成一个随机的字符串,n代表位数
create function rand_string(n int) returns varchar(255)
begin
    declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    declare return_str varchar(255) default '';
    declare i int default 0 ;
    while i&lt; n do
        set     return_str=concat(return_str,substring(chars_str,floor(1+rand()*52),1));
        set i = i +1 ;
    end while ;
    return return_str ;
end$$

#生成一个随机的数字
create function rand_num() returns int(5)
begin
    declare i int default 0 ;
    set i = floor(100+rand()*10);
    return i ;
end$$

#创建插入数据的存储过程
crate procedure insert_emp(IN start int(10),IN max_num int(10))
begin
    declare i int default 0 ;
    set autocommit = 0 ;
    repeat
        set i = i +1 ;
        insert into emp(name,age) values(rand_string(6),(start+i));
        until i = max_num
    end repeat;
    commit;
end$$

call insert_emp(100,5000000);</code></pre>
</div>
</div>
<div class="paragraph">
<p><strong>定义结束符号</strong></p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-mysql" data-lang="mysql">DELIMITER $$
或
DELIMITER //</code></pre>
</div>
</div>
<div class="paragraph">
<p>默认情况下，delimiter &#8220;;&#8221; 用于向 MySQL 提交查询语句。在存储过程中每个
SQL 语句的结尾都有个 &#8220;;&#8221;，如果这时候，每逢 &#8220;;&#8221; 就向 MySQL
提交的话，当然会出问题了。</p>
</div>
<div class="paragraph">
<p><strong>声明过程或函数</strong></p>
</div>
<div class="listingblock">
<div class="content">
<pre class="highlightjs highlight"><code class="language-mysql" data-lang="mysql">create procedure 存储过程名(参数)

create function 存储函数名(参数)</code></pre>
</div>
</div>
<div class="paragraph">
<p>过程中可以应用多个函数。可以将过程视为开始和结束一个完成流程，而函数则是其中一个组成元素。</p>
</div>
<div class="paragraph">
<p><strong>调用</strong></p>
</div>
<div class="paragraph">
<p><code>call 存储过程名(参数)</code></p>
</div>
<div class="paragraph">
<p><strong>过程和函数中的语句</strong></p>
</div>
<div class="paragraph">
<p><strong>参数</strong></p>
</div>
<div class="paragraph">
<p><code>CREATE PROCEDURE one_name(in param1 int, in param2 varchar(10), ...)</code></p>
</div>
<div class="ulist">
<ul>
<li>
<p>IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）</p>
</li>
<li>
<p>OUT
输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）</p>
</li>
<li>
<p>INOUT
输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）</p>
</li>
</ul>
</div>
<div class="paragraph">
<p><strong>结构体</strong></p>
</div>
<div class="paragraph">
<p>以begin开始，以end结束(可嵌套)</p>
</div>
<div class="paragraph">
<p><strong>定义变量</strong></p>
</div>
<div class="paragraph">
<p>DECLARE variable_name datatype [DEFAULT value];</p>
</div>
<div class="paragraph">
<p><strong>定义赋值</strong></p>
</div>
<div class="paragraph">
<p>SET 变量名 = 表达式值</p>
</div>
<div class="paragraph">
<p><strong>变量作用域</strong></p>
</div>
<div class="paragraph">
<p>最近的一个BEGIN_END结构体内。</p>
</div>
<div class="paragraph">
<p><strong>逻辑语句</strong></p>
</div>
<div class="ulist">
<ul>
<li>
<p>if-then-else-end if 语句</p>
</li>
<li>
<p>case-when-end cash语句：</p>
</li>
<li>
<p>while do ···· end while</p>
</li>
<li>
<p>repeat… until···· end repeat</p>
</li>
<li>
<p>loop ·····endloop</p>
</li>
<li>
<p>LABLES 标号 标号可以用在 begin repeat while 或者 loop
语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。</p>
</li>
<li>
<p>ITERATE迭代</p>
</li>
</ul>
</div>
</div>
<div class="sect2">
<h3 id="_mysql_深入研究记录">1.4. MySQL 深入研究记录</h3>
<div class="quoteblock">
<blockquote>
<div class="paragraph">
<p>提升查询速度，本质是减少磁盘IO次数</p>
</div>
</blockquote>
</div>
</div>
</div>
</div>
<div class="sect1">
<h2 id="_索引">2. 索引</h2>
<div class="sectionbody">
<div class="quoteblock">
<blockquote>
<div class="paragraph">
<p>索引是帮助MySQL搞笑获取数据的<strong>排好序</strong>的<strong>数据结构</strong></p>
</div>
</blockquote>
</div>
<div class="paragraph">
<p>索引的数据结构</p>
</div>
<div class="ulist">
<ul>
<li>
<p>二叉树（极端情况，会退化成链表，并不能减少查询次数）</p>
</li>
<li>
<p>红黑树（二叉平衡树，会根据左右树高度进行平衡，但是数量越大，树的深度就越高，查询次数也会越来越高）</p>
</li>
<li>
<p>Hash表</p>
</li>
<li>
<p>B-Tree</p>
</li>
<li>
<p>B+</p>
</li>
</ul>
</div>
</div>
</div>
<div class="sect1">
<h2 id="_mysql_底层采用ｂ_tree_来存储索引">3. MySQL 底层采用Ｂ+ Tree 来存储索引</h2>
<div class="sectionbody">
<div class="ulist">
<ul>
<li>
<p>非叶子节点不存在data，只存储索引（冗余），可以放更多的索引</p>
</li>
<li>
<p>叶子节点包含所有索引字段</p>
</li>
<li>
<p>叶子节点用指针连接，提高区间访问性能</p>
</li>
</ul>
</div>
<div class="imageblock">
<div class="content">
<img src="https://wanghengzhi.com/upload/2022/03/%E5%9B%BE%E7%89%87-daf03927e0f54be9815c8b35af525424.png" alt="图片.png">
</div>
<div class="title">Figure 1. 图片.png</div>
</div>
<div class="ulist">
<ul>
<li>
<p>叶子节点的数据按照顺序从左到右，数据依次由小到大。</p>
</li>
<li>
<p>每个节点的大小约为16kb</p>
</li>
<li>
<p>树的高度即为磁盘io次数，也即查找次数，通过b+树，树高度在3层，就支持2kw的数据。</p>
</li>
</ul>
</div>
</div>
</div>
<div class="sect1">
<h2 id="_innodb_和_myisam_存储引擎">4. InnoDB 和 MyISAM 存储引擎</h2>
<div class="sectionbody">
<div class="quoteblock">
<blockquote>
<div class="paragraph">
<p>形容数据库表结构</p>
</div>
</blockquote>
</div>
<div class="paragraph">
<p>MyISAM 存储结构</p>
</div>
<div class="ulist">
<ul>
<li>
<p>table.frm 表结构</p>
</li>
<li>
<p>table.MYD 表数据</p>
</li>
<li>
<p>table.MYI 表索引</p>
</li>
</ul>
</div>
<div class="paragraph">
<p>先走索引文件找到文件存储磁盘位置，然后在MYD文件中找到对应的记录。</p>
</div>
<div class="paragraph">
<p>InnoDB 存储结构</p>
</div>
<div class="ulist">
<ul>
<li>
<p>table.frm 表结构</p>
</li>
<li>
<p>table.ibd 索引加数据文件</p>
</li>
</ul>
</div>
</div>
</div>
<div class="sect1">
<h2 id="_聚集索引和非聚集索引的区别">5. 聚集索引和非聚集索引的区别</h2>
<div class="sectionbody">
<div class="paragraph">
<p>聚集索引：将数据存储和索引放在一起，并且按照一定的顺序组织，找到索引也就找到了数据，数据的物理顺序和索引的顺序是一致的，只要索引是相邻的，那么对应的数据也一定是相邻的存放在磁盘上。
非聚集索引：索引和数据分开存储，索引只存储数据所在磁盘位置。根据索引找到数据行的位置，再通过回表查询磁盘所在的数据。</p>
</div>
<div class="paragraph">
<p>优势：</p>
</div>
<div class="olist arabic">
<ol class="arabic">
<li>
<p>查询通过聚集索引可以直接获取数据，相比非聚集索引需要二次查询效率要高</p>
</li>
<li>
<p>聚集索引堆范围查询的效率很高，因为其数据是按照大小排列的</p>
</li>
<li>
<p>聚集索引适合用在排序的场合</p>
</li>
</ol>
</div>
<div class="paragraph">
<p>劣势：</p>
</div>
<div class="olist arabic">
<ol class="arabic">
<li>
<p>维护索引很昂贵，因为插入新行或主键更新导致树结构的分裂和平衡。</p>
</li>
<li>
<p>采用UUid作为主键，会出现聚集索引可能比全表扫描更慢</p>
</li>
</ol>
</div>
<div class="paragraph">
<p>为什么InnoDB需要一个主键？并推荐使用整型的自增主键？</p>
</div>
<div class="paragraph">
<p>因为InnoDB表数据文件本身就是按B+树组织的一个索引结构文件，如果不使用主键，MySQL也会创建一个隐藏的主键。整型比较效率高于字符串，其二存储空间一般小于其他UUID。自增，当节点数据满了后，分裂平衡效率更高。</p>
</div>
</div>
</div>
<div class="sect1">
<h2 id="_b_树_vs_b树">6. B+ 树 vs B树</h2>
<div class="sectionbody">
<div class="ulist">
<ul>
<li>
<p>非叶子节点不存储数据，这样存储的索引数量更多，能存储更多的数据，得到的树层级更低</p>
</li>
<li>
<p>叶子节点增减一个节点之间的双向连接。</p>
</li>
</ul>
</div>
</div>
</div>
<div class="sect1">
<h2 id="_联合索引">7. 联合索引</h2>
<div class="sectionbody">
<div class="paragraph">
<p>多个字段组合，按顺序进行排序。这里顺序是指多个字段组合起来的顺序，因此，必须符合最左匹配原则，如果用后面的字段是没有顺序的，无法走索引。</p>
</div>
</div>
</div>
<div class="sect1">
<h2 id="_索引的设计原则">8. 索引的设计原则</h2>
<div class="sectionbody">
<div class="quoteblock">
<blockquote>
<div class="paragraph">
<p>查询更快、占用空间更小</p>
</div>
</blockquote>
</div>
<div class="olist arabic">
<ol class="arabic">
<li>
<p>适合索引的列是出现在where子句中的列，或者连接子句中指定的列</p>
</li>
<li>
<p>基数较小的类，索引效果较差，没必要在此列建立索引</p>
</li>
<li>
<p>使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，节省索引空间</p>
</li>
<li>
<p>不要过度索引，索引需要额外的磁盘空间，并降低写操作，在修改表内容时，索引会更新甚至重构，索引列越多，时间越长，所以保持需要的索引即可</p>
</li>
<li>
<p>定义有外键的数据列一定要建立索引</p>
</li>
<li>
<p>更新频繁的字段不适合建立索引</p>
</li>
<li>
<p>区分度太低的列也不适合，也即列中可能的值很少，区域固定</p>
</li>
<li>
<p>尽量扩展索引，不要新建索引</p>
</li>
<li>
<p>查询少、重复值多的不要建立索引</p>
</li>
<li>
<p>对于text等大字段不要建立索引</p>
</li>
</ol>
</div>
</div>
</div>
<div class="sect1">
<h2 id="_锁">9. 锁</h2>
<div class="sectionbody">
<div class="paragraph">
<p>基于锁的属性：共享锁、排他锁 基于锁的粒度：
行级锁，表级锁，页级锁，记录锁，间隙锁，临建锁
基于锁的状态分类：意向共享锁，意向排他锁</p>
</div>
<div class="paragraph">
<p>共享锁：</p>
</div>
<div class="paragraph">
<p>共享锁又称读锁，当一个事务对数据加上读锁后，其他事务只能对其数据加读锁，而不能对数据加写锁，知道所有的读锁释放后，其他事务才能加写锁。共享锁主要是为了支持并发读取数据，读取数据时不支持修改，避免出现重复读问题。</p>
</div>
<div class="paragraph">
<p>排他锁：</p>
</div>
<div class="paragraph">
<p>排他锁又称写锁，当一个事务为数据加上排他锁，其他事务不能为数据加任何锁，直到该锁释放。排他锁的目的是在数据修改时，不允许其他事务修改，也不允许其他事务读取，避免出现脏读或者脏数据问题。</p>
</div>
<div class="paragraph">
<p>表锁：</p>
</div>
<div class="paragraph">
<p>将整个表进行加锁 ，下一个事务必须等当前加锁的事务释放后才能进行访问</p>
</div>
<div class="paragraph">
<p>行锁：</p>
</div>
<div class="paragraph">
<p>锁住表的一行或多行，其他事务不能访问上锁的行，其他可以访问。特点：粒度小，加锁麻烦，不容易冲突，支持并发高</p>
</div>
<div class="paragraph">
<p>记录锁：</p>
</div>
<div class="paragraph">
<p>也属于行锁的一种，只是只能锁住一条数据，加了记录锁的数据后可以避免查询的时候被修改导致重复读问题，也避免了在修改事务未提交之前，其他事务脏读问题。</p>
</div>
<div class="paragraph">
<p>页锁：</p>
</div>
<div class="paragraph">
<p>粒度介于表锁和行锁之间，锁定相邻的一组记录。</p>
</div>
</div>
</div>
<div class="sect1">
<h2 id="_explain">10. explain</h2>
<div class="sectionbody">
<div class="paragraph">
<p><a href="https://www.jianshu.com/p/ea3fc71fdc45" class="bare">https://www.jianshu.com/p/ea3fc71fdc45</a></p>
</div>
<div class="sect2">
<h3 id="_mysql_数据处理原理">10.1. MySQL 数据处理原理</h3>
<div class="paragraph">
<p><a href="https://blog.csdn.net/a783295110/article/details/113591869" class="bare">https://blog.csdn.net/a783295110/article/details/113591869</a></p>
</div>
<div class="paragraph">
<p><a href="https://zhuanlan.zhihu.com/p/141599198" class="bare">https://zhuanlan.zhihu.com/p/141599198</a></p>
</div>
</div>
</div>
</div>
</div>
<div id="footer">
<div id="footer-text" style="text-align: center;">
<a href="https://beian.miit.gov.cn">蜀 ICP 备 2024094310 号</a> | <a href="http://www.beian.gov.cn/portal/registerSystemInfo?recordcode=51160302511654">川公网安备 51160302511654 号</a>  | 联系邮箱: zerothreeoneone@foxmail.com | 
更新于 2024-08-22 22:37:27 CST
</div>
</div>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/highlight.js/8.9.1/styles/github.min.css">
<script src="https://cdnjs.cloudflare.com/ajax/libs/highlight.js/8.9.1/highlight.min.js"></script>
<script>hljs.initHighlighting()</script>
</body>
</html>